Creating running totals using a formula
If you have suppressed data, or your data is based on a formula that occurs WhilePrintingRecords, you should create a running total formula rather than using the Running Total Select Expert.
When you create a running total manually, you need to create three formulas:
- a summary formula
- a reset formula to set a variable to zero
- a display formula to display the variable.
In the following procedure, you will create a report that performs the following functions:
- maintains a running total of customer orders
- groups customer orders and resets the running total for each group
- displays the subtotal for each order (the last running total for that order).
To create running totals using a formula
- Create a report using the sample data, Xtreme.mdb. Link the Customers and Orders tables and place the following fields from left to right in the Details section:
{customer.CUSTOMER NAME}
{orders.ORDER ID}
{orders.ORDER AMOUNT}
- On the Insert menu, click Field Object.
Select Formula Fields in the Field Explorer dialog box and click New.
- Name the formula "RunningTotal" and click OK.
- Enter the following into the Formula Editor:
WhilePrintingRecords;
CurrencyVar Amount;
Amount := Amount + {orders.ORDER AMOUNT};
Click Save and Close.
- Place this formula in the Details section of your report, just to the right of the {orders.ORDER AMOUNT} field.
This formula prints the running total of the values in the Order Amount field.
On the Insert menu, click Group and group the report on the {customer.CUSTOMER NAME} field.
- In the Formula Editor, create "AmountReset":
WhilePrintingRecords;
CurrencyVar Amount := 0;
This formula says:
Set the value in the Amount variable to 0.
- Place this formula in the Group Header #1 section of your report.
Because the Group Header #1 section appears once for every group, @AmountReset will execute each time the group changes. Thus, the Amount variable is reset to 0 each time a new group begins.
- Select the @AmountReset formula on the report and use the Format Editor to suppress it so that it will not appear in the final print-out.
- In the Formula Editor, create "AmountDisplay":
WhilePrintingRecords;
CurrencyVar Amount;
This formula simply displays the current value of the Amount variable at any time.
- Place this formula in the Group Footer #1 section of your report.
Because the Group Footer #1 section appears once for every group, @AmountDisplay will execute each time a group ends. Thus, the value stored in the Amount variable will be printed each time the group changes.
Note: This formula prints the same value that @RunningTotal prints as the running total for the last record in each group. But since it is printing it in the Group Footer section, it acts as a group subtotal, not as a running total.